运行示例的数据

Data for Our Running Example

我们将使用表2-1中的数据来构建关系和图数据模型。

We will use the data in Table 2-1 to construct relational and graph data models.

对于我们的第一个用例，数据描述了金融服务行业中几个客户的资产。客户可以共享账户和贷款，但一张信用卡只能由一个客户使用。

For our first use case, the data describes several customers’ assets in the financial services industry. The customers can share accounts and loans, but a credit card can be used by only one customer.

让我们看一下几行数据。表 2-1显示了有关五位客户的数据。这五位客户及其数据将用于构建数据模型并说明本章和接下来三章中的新概念。

Let’s look at a few rows of the data. Table 2-1 displays data about five customers. These five customers and their data will be used to build data models and illustrate new concepts throughout this chapter and the next three chapters.

表 2-1中显示的五行样本数据中有五个唯一客户。其中一些客户共享账户或贷款，以说明我们在金融服务系统中通常看到的不同类型的用户。

There are five unique customers in the five rows of sample data shown in Table 2-1. Some of these customers share accounts or loans to illustrate different types of users we typically see in a financial services system.

例如，customer_0和customer1，或者 Michael 和 Maria，代表典型的父子关系；Michael 是父母，Maria 是孩子。关于customer_2Rashika 的数据表明他们是这项金融服务的唯一用户。我们通常在大型应用程序中看到这种类型的用户数量最高；像 Rashika 这样的客户只拥有客户独有的数据，不会与任何其他人共享。最后，customer_3和customer_4（Jamie 和 Aaliyah）共享一个账户和一笔贷款。这种类型的数据通常表明用户是加入其金融账户的合作伙伴。

For example, customer_0 and customer1, or Michael and Maria, represent a typical parent-child relationship; Michael is the parent, and Maria is the child. The data about customer_2, Rashika, indicates they are a sole user of this financial service. We usually see the highest volume of this type of user in large applications; customers like Rashika only have data that is unique to the customer and is not shared by anyone else. Last, customer_3 and customer_4 (Jamie and Aaliyah) share an account and a loan. This type of data typically indicates that the users are partners who have joined their financial accounts.

如果这是您公司的样本数据，想象一下您可能与同事就建模这些数据进行的对话。在这种情况下，您正在共享白板或其他说明工具，并试图绘制数据中的实体、属性和关系。无论您是否使用关系或图系统，您都可能会进行类似于图 2-1中的概念模型的讨论。

If this were your company’s sample data, imagine the conversation you might have with your coworker about modeling this data. In this scenario, you are sharing a whiteboard, or other illustrative tool, and you are trying to map out the entities, attributes, and relationships within the data. Whether or not you use a relational or graph system, you likely would be having a discussion similar to the conceptual model in Figure 2-1.

从表 2-1中，我们发现了四个主要实体：客户、账户、贷款和信用卡。这些实体都与客户有关系。客户可以有多个账户，而这些账户可以有多个客户。客户还可以有多笔贷款，而这些贷款可以有多个客户。最后，客户可以有多张信用卡，但每张信用卡都是针对一个客户的。

From Table 2-1, we find four main entities: customers, accounts, loans, and credit cards. These entities each have relationships tied to the customer. Customers can have multiple accounts, and those accounts can have more than one customer. Customers can also have multiple loans, and those loans can have more than one customer. Finally, customers can have multiple credit cards, but each credit card is unique to one customer.

图 2-1. 表2-1数据中观察到的关系的概念描述

实体和属性

Entities and Attributes

一般来说，数据建模技术通过描述数据中的实体及其属性来帮助您描述现实世界。每个概念都有特定的含义：

Generally speaking, data modeling techniques help you describe the real world by describing the entities and their attributes within your data. Each of those concepts has a specific meaning:

实体

Entity

实体是您需要在数据库中跟踪的对象，例如人、地点或事物。

An entity is an object such as a person, place, or thing that you need to track in your database.

属性

Attribute

属性是指实体的性质，例如名称、日期或其他描述性特征。

An attribute refers to a property of an entity such as names, dates, or other descriptive features.

关系数据建模的传统方法首先是识别数据中的实体（人物、地点和事物）以及这些实体的属性（名称、标识符和描述）。实体可以是客户、银行账户或产品。属性是诸如人名或银行账户号之类的概念。

The traditional approach for relational data modeling starts with identifying the entities (people, places, and things) in your data and the attributes (names, identifiers, and descriptions) of those entities. Entities could be customers, bank accounts, or products. Attributes are concepts such as a person’s name or bank account number.

在本次数据建模练习中，我们首先从表 2-1开始对两个实体进行建模：客户和银行账户。在关系系统中，我们传统上将实体视为表。如图2-2所示。

For this exercise in data modeling, let’s start by modeling two entities from Table 2-1: customers and bank accounts. In a relational system, we traditionally view the entities as tables. This is illustrated in Figure 2-2.

图 2-2。在应用程序中建模数据的传统方法：识别实体及其属性

图 2-2中显示了两个主要概念：实体及其各自的属性。此图中有两个实体：客户和账户。对于每个实体，都有一个描述该实体的属性列表。客户可以用唯一标识符、姓名、出生日期等来描述。账户也有描述性属性：唯一账户标识符和账户创建日期。

There are two main concepts shown in Figure 2-2: entities and their respective attributes. There are two entities in this diagram: customers and accounts. For each entity, there is a list of attributes that describe the entity. A customer can be described by a unique identifier, name, birthdate, and so on. There are also descriptive attributes for accounts: a unique account identifier and the date the account was created.

在关系数据库中，每个实体都成为一张表。表的行包含有关该实体的样本数据，每列包含描述性属性的值。

In a relational database, each entity becomes a table. The rows of the table contain sample data about that entity, and each column contains values for the descriptive attributes.

建立 ERD

Building Up to an ERD

在现实世界中，客户拥有自己的账户。设计关系数据库的下一步是概念性地对这种联系进行建模。我们需要在模型中添加一种描述一个人如何拥有银行账户的方法。图 2-3显示了一种流行的从客户到账户的建模方法。

In the real world, customers own accounts. The next step in designing a relational database would be to conceptually model this connection. We need to add to our model a way to describe how a person owns a bank account. A popular method for modeling the link from customers to accounts is shown in Figure 2-3.

图 2-3客户和银行账户的实体关系图

我们在图 2-2和图 2-3之间添加的一个视觉元素是连接人员和帐户实体表的菱形。此连接表示数据库中的客户和帐户之间存在链接。即客户拥有帐户。

One visual element that we added between Figure 2-2 and Figure 2-3 is the diamond that connects the person and account entity tables. This connection indicates that there is a link between customers and accounts in the database. Namely, customers own accounts.

该图像的其他视觉细节包括人员表和连接之间的双线owns。在这里我们看到一个n，连接m的另一侧有一个owns。此符号表示这是many-to-many客户和帐户之间的连接。具体来说，这意味着一个人可以拥有多个帐户，而一个帐户可以由多个客户拥有。

The image’s other visual details include the double lines between the person table and the owns connection. Here we see an n, with an m on the opposite side of the owns connection. This notation indicates that this is a many-to-many connection between customers and accounts. Specifically, this translates to the idea that one person can own many accounts and that one account can be owned by many customers.

关于实现细节的以下细微差别很重要：ERD 中显示的链接转换为表或外键。也就是说，客户与其帐户之间的联系以表的形式存储在关系系统中。这意味着该owns表本质上转换为数据库中的另一个实体。

The following nuance about the implementation details is important: links that are shown in ERDs translate to tables or foreign keys. That is, the connections between customers and their accounts are stored as a table within a relational system. This means that the owns table essentially translates to another entity in the database.

尽管几十年来我们被迫这样思考，但还有更好的方法。

Even though we have been forced to think this way for decades, there are better ways.

让我们重新回顾表 2-1中的数据。不过，这次我们将使用这些数据来说明图数据中的概念，然后说明如何在图数据库中对数据进行建模。

Let’s revisit the data from Table 2-1. However, this time we are going to use the data to illustrate concepts in graph data, followed by how we will model the data in a graph database.

图的基本元素

Fundamental Elements of a Graph

我们需要介绍的第一个概念是图和图数据的基本元素及其定义。这些术语被图社区的所有成员使用，并被接受为图的基本元素。

The first concepts we need to introduce are the fundamental elements of graphs and graph data and their definitions. These terms are used across all members of the graph community and are accepted as the fundamental elements of a graph.

图

Graph

图是用两个不同元素来表示数据：顶点和边。

A graph is a representation of data with two distinct elements: vertices and edges.

顶点

Vertex

顶点（复数 vertices）表示数据中的概念或实体。

A vertex (pl. vertices) represents a concept or entity in data.

边缘

Edge

边表示从一个顶点到另一个顶点的关系或链接。

An edge represents a relationship or link from one vertex to another.

您已经看到了我们正在讨论的基本要素。图 2-4中的财务数据包含四个概念实体：客户、账户、信用卡和贷款。这些实体自然转化为我们图的顶点。

You have already seen the fundamental elements we are talking about. Our financial data from Figure 2-4 contains four conceptual entities: customers, accounts, credit cards, and loans. These entities naturally translate into the vertices of our graph.

接下来，我们使用边来连接顶点。这些连接说明了数据片段之间存在的关系。在图数据中，边连接两个顶点，作为两个对象之间关系的抽象表示。

Next, we use edges to connect our vertices. These connections illustrate the relationships that exist between the pieces of data. In graph data, an edge connects two vertices as an abstract representation of a relationship between the two objects.

对于这些数据，我们将使用边来显示个人与其财务数据之间的关系。我们对数据进行建模，表明客户拥有账户、客户欠贷款以及客户使用信用卡。图数据库中的边成为owns、owes和 的关系uses。

For this data, we will use edges to show the relationship between a person and their financial data. We model the data to say that the customer owns accounts, the customer owes loans, and the customer uses credit cards. The edges in the graph database become the relationships of owns, owes, and uses.

数据中的所有顶点和边共同代表完整的graph。

Together, all of the vertices and edges in the data represent the full graph.

邻接

Adjacency

虽然图论中有许多基础主题可供探索，但首先要探索的术语是邻接。您会发现这个术语在整个图论中都用于讨论数据如何连接。本质上，邻接性是用于描述顶点是否相互连接的数学术语。正式定义如下：

While there are many foundational topics in graph theory to explore, the term to start with is adjacency. You will find this term used throughout graph theory to talk about how data is connected. Essentially, adjacency is the mathematical term used to describe whether vertices are connected to each other. Formally, it is defined as follows:

邻接

Adjacency

如果两个顶点通过边连接，则它们相邻。

Two vertices are adjacent if they are connected by an edge.

在图 2-4中，Maria与 相邻acct_14。此外，我们看到Michael和都Maria与 相邻，acct_14因为它们都拥有该帐户。当您能够看到不同实体如何以您以前可能未曾见过的方式相互关联时，在应用程序中使用图数据的好处会立即显现出来。

In Figure 2-4, Maria is adjacent to acct_14. Also, we see that both Michael and Maria are adjacent to acct_14 because they both own that account. The benefit to using graph data in your application is immediately apparent when you can see how different entities are related in a way that you may not have previously seen.

邻接的概念将在本书中多次出现，涉及从数据连通性到磁盘上的不同存储格式等主题。目前，重要的是只知道这个流行术语指的是顶点如何连接。

The idea of adjacency will come up many more times throughout this book, in topics ranging from the connectedness of data to different storage formats on disk. For now, it is important to know only that this popular term refers to how vertices are connected.

距离

Distance

邻里的概念使我们认识到距离。讨论此样本数据的连通性的另一种方法是说出从一个顶点走到另一个顶点需要多少步。讨论Michael的第一或第二邻域与查找距离 为 1 或 2 的所有顶点相同Michael。

The concept of neighborhoods brings us to distance. Another way to talk about the connectedness of this sample data is to say how many steps it takes to walk from one vertex to another. Talking about Michael’s first or second neighborhood is the same as finding all vertices that are a distance of 1 or 2 from Michael.

距离

Distance

在图数据中，距离是指从一个顶点到另一个顶点必须经过的边数。

In graph data, distance refers to the number of edges that you have to walk through to get from one vertex to another.

在图 2-5中，我们选择顶点 为起点Michael。顶点cc_17、loan_32和acct_14位于Michael的第一个邻域中，与 的距离相同，为 1 Michael。

In Figure 2-5, we selected the starting point as the vertex Michael. The vertices cc_17, loan_32, and acct_14 are in Michael’s first neighborhood, which is the same as a distance of 1 from Michael.

在数学界，你会看到它被写成dist(Michael, cc_17) = 1。这也意味着从你的起点开始的第二个邻域中的所有东西都有两个边的距离，依此类推——具体来说，dist(Michael, Maria) = 2。

In mathematical communities, you will see this written as dist(Michael, cc_17) = 1. That also means that everything in the second neighborhood from your starting point is two edges away, and so on—specifically, dist(Michael, Maria) = 2.

程度

Degree

邻接、邻域和距离的概念有助于我们了解两部分数据是否相连。对于许多应用来说，了解一段数据与其相邻数据的连接程度尤其有用。

The ideas of adjacency, neighborhoods, and distance help us understand if two pieces of data are connected. For many applications, it is especially useful to understand how well a piece of data is connected to its neighbors.

数据是否连接以及连接程度之间的差异引入了数学界的一个新术语：度。

The difference between if and how well data is connected introduces a new term from the math community: degree.

程度

Degree

顶点的度是与该顶点相邻（即接触）的边的数量。

A vertex’s degree is the number of edges that are incident to (i.e., touch) the vertex.

换句话说，我们讨论顶点的度时是根据与该顶点相切的边的数量。

In other words, we talk about a vertex’s degree in reference to the number of edges that touch that vertex.

在我们讨论接下来的例子时，回想一下图 2-4。在该图中，我们看到有三条边连接Michael到cc_17、loan_32和acct_14。我们应用这一点来表示 的度Michael为三，或deg(Michael) = 3。

Recall Figure 2-4 as we walk through the upcoming examples. In that figure, we see that there are three edges that connect Michael to cc_17, loan_32, and acct_14. We apply this to say that the degree of Michael is three, or deg(Michael) = 3.

在这个数据中，我们有两个顶点的度为 2。具体来说，与和acct_14相邻，因此它的度为 2。在图像的右侧，我们看到也只有两条边。这意味着 的度为 2。MichaelMariaRashikaRashika

In this data, we have two vertices that have a degree of two. Specifically, acct_14 is adjacent to Michael and Maria, so it has a degree of two. On the right side of the image, we see that Rashika also has only two edges. That means that Rashika has a degree of two.

我们的示例数据中共有 5 个顶点的度为 1。它们分别是loan_32、cc_17、Maria、cc_32和acct_5。

There are a total of five vertices in our example data that have a degree of one. They are loan_32, cc_17, Maria, cc_32, and acct_5.

我们还根据边是从该特定顶点开始还是结束，将顶点的度数细分为两个子类别。让我们介绍两个描述这些类别的新术语。

We also break down a vertex’s degree into two subcategories according to whether the edge starts at or ends with that particular vertex. Let’s introduce two new terms that describe these categories.

入度

In-degree

顶点的入度是与该顶点相邻（或接触）的入边的总数。

A vertex’s in-degree is the total number of incoming edges that are incident to (or touch) the vertex.

出度

Out-degree

顶点的出度是与该顶点相邻（或接触）的出边的总数。

A vertex’s out-degree is the total number of outgoing edges that are incident to (or touch) the vertex.

让我们将这些定义应用到我们刚刚讨论过的例子。

Let’s apply these definitions to the examples we just walked through.

的三条边都Michael始于Michael并终止于其他顶点：cc_17、loan_32和acct_14。因此，我们说 的出度为Michael三，因为所有三条边都是出度。

All three of Michael’s edges start at Michael and end at other vertices: cc_17, loan_32, and acct_14. Therefore, we say that the out-degree of Michael is three because all three of the edges are outgoing.

的入度为acct_142，因为它有来自Michael和 的入边Maria。 的两条Rashika边都是出边，所以我们说 的Rashika出度为 2。

The in-degree of acct_14 is two because it has incoming edges from Michael and Maria. Both of Rashika’s edges are outgoing edges, so we say that Rashika has an out-degree of two.

的入度为 1，因为该边是入边； 、和 也是cc_17如此。的出度为 1，因为其一条边是 的出边。loan_32cc_32acct_5Mariaacct_14

The in-degree of cc_17 is one because the edge is incoming; the same is true for loan_32, cc_32, and acct_5. Maria has an out-degree of one because its one edge is an outgoing edge to acct_14.

顶点标签和边标签

Vertex Labels and Edge Labels

图数据的基本元素（顶点和边）为我们提供了图模式语言 (GSL) 的第一个术语：顶点标签和边标签。关系模型使用表来描述图 2-3中的数据，而我们使用顶点标签和边标签来描述图的模式。

The fundamental elements of graph data—vertices and edges—give us the first terms of the Graph Schema Language (GSL): vertex labels and edge labels. Where relational models use tables to describe the data in Figure 2-3, we use vertex labels and edge labels to describe a graph’s schema.

顶点标签

Vertex label

顶点标签是一组语义上同质的对象。也就是说，顶点标签表示一类具有相同关系和属性的对象。

A vertex label is a set of objects that are semantically homogeneous. That is, a vertex label represents a class of objects that share the same relationships and attributes.

边缘标签

Edge label

边标签命名数据库模式中顶点标签之间的关系类型。

An edge label names the type of relationship between vertex labels in your database schema.

在图建模中，我们用顶点标签标记每个实体，并用边标签描述实体之间的关系。

In graph modeling, we label each entity with a vertex label and describe the relationship between entities with an edge label.

一般而言，顶点标签描述数据中具有相同类型属性和相同标签关系的实体。边标签描述顶点标签之间的关系。

Generally speaking, vertex labels describe entities in your data that share attributes of the same type and relationships of the same label. Edge labels describe relationships between vertex labels.

对于表 2-1中的数据，我们将使用图 2-6中所示的概念图模型对同一客户和帐户进行建模。此概念图模型看起来与图 2-3中的 ERD 非常相似，但转换为使用 GSL 的前两个术语。

For the data from Table 2-1, we would model the same customer and account with the conceptual graph model shown in Figure 2-6. This conceptual graph model looks very similar to the ERD from Figure 2-3, but with the translation to using the first two terms of GSL.

图 2-6：显示客户和银行账户的顶点和边标签的图模型

在 GSL 中，顶点标签用一个圆圈表示，圆圈内包含标签的名称。图 2-6Customer显示了和顶点标签的图示Account。边标签用两个顶点标签之间的命名线表示。我们在图 2-6owns中看到，边标签位于和Customer顶点标签之间。当我们查看此图示时，我们推断客户与帐户有关系；具体来说，客户拥有该帐户。稍后我们将在“边方向”Account中讨论边的方向。

In the GSL, a vertex label is illustrated with a circle containing the label’s name. Figure 2-6 shows this for the Customer and Account vertex labels. An edge label is illustrated with a named line between two vertex labels. We see this in Figure 2-6 with the owns edge label between the Customer and Account vertex labels. When we look at this illustration, we infer that a customer has a relationship to an account; specifically, the customer owns the account. We will get into an edge’s direction later, in “Edge Direction”.

特性

Properties

在图 2-3中，关系模型使用属性来描述数据，而在图建模中，属性则用来描述数据。也就是说，以前我们使用的是属性，而在图模型中，我们使用的是属性。

Where relational models use attributes to describe the data in Figure 2-3, properties describe data in graph modeling. That is, where we had attributes before, we have properties in a graph model.

财产

Property

属性描述顶点标签或边标签的特征，例如名称、日期或其他描述性特征。

A property describes features of a vertex label or an edge label, such as names, dates, or other descriptive features.

在图 2-7中，每个顶点标签都有一个与之关联的属性列表。这些属性与图 2-3中的关系 ERD 中的属性相同。客户顶点由其唯一标识符、姓名和出生日期描述。与前面一样，帐户由其帐户 ID 和创建日期描述。我们添加一个边标签owns来描述此数据模型中两个实体之间的关系。

In Figure 2-7, each vertex label has a list of properties associated with it. These properties are the same attributes from the relational ERD in Figure 2-3. A customer vertex is described by its unique identifier, name, and birthdate. An account is described with its account ID and created date, as before. We add an edge label of owns to describe the relationship between the two entities in this data model.

图 2-7客户和银行账户的图模型

边缘方向

Edge Direction

GSL 中定义的下一个建模概念是边的方向。当我们在数据模型中设置边标签时，我们会将顶点标签连接在一起，这种方式符合我们自然谈论数据的方式；我们会说一个客户拥有一个帐户，然后我们用这种方式在图中对数据进行建模。这也赋予了每条边一个方向。

The next modeling concept defined in the GSL is an edge’s direction. When we set up our edge labels in our data model, we connected the vertex labels together in a way that follows how we would naturally talk about the data; we would say that a customer owns an account, and we modeled the data in our graph that way. This also gives each edge a direction.

有两种方法可以对边的方向进行建模：有向和双向。

There are two ways in which you can model the direction of an edge: directed and bidirectional.

导演

Directed

有向边只有一个方向：从一个顶点标签到另一个顶点标签。

A directed edge goes one way: from one vertex label to the other vertex label.

双向

Bidirectional

双向或双向边在顶点标签之间双向延伸。

A bidirectional or bidirected edge goes in both directions between the vertex labels.

使用 GSL，我们在边线的一端或两端用箭头指示边的方向。图 2-8中用边标签下方的箭头说明了这一点。

Using GSL, we indicate the direction of an edge with an arrow at either one end or both ends of the edge line. This is illustrated in Figure 2-8 with the arrow below the edge label.

图 2-8.使用带有单箭头的线来指示边标签的方向

我们可以认为图 2-8中的示例显示了一个有向边标签。我们有一个从客户到帐户的边标签。此边标签使用有向边来模拟客户拥有帐户。

We would say that our example in Figure 2-8 shows a directed edge label. We have an edge label that goes from the customer to an account. This edge label uses a directed edge to model that a customer owns an account.

另一方面，以相反的方向对数据进行建模可能会很有用。一种方法是添加从帐户到客户的第二条有向边。该边表示该帐户归客户所有，如图2-9所示。我们说图 2-9中的所有边都是有向的，因为它们只朝一个方向。

On the other hand, it might be useful to model our data in the opposite direction. One way to do this is by adding a second directed edge from the account to the customer. This edge indicates that the account is owned by the customer, like in Figure 2-9. We say that all of the edges in Figure 2-9 are directed because they go only one way.

图 2-9。使用两个不同的有向边标签允许在客户和账户之间双向行走

边标签的方向来自于我们如何交流数据。描述数据时，使用主语、谓语（即动词）和宾语来交流域。要了解这一点，请考虑如何描述我们在本章迄今为止一直使用的示例数据。你可能会想“客户拥有账户”或“银行账户归客户所有”。在第一个短语中，主语是“客户”，谓语是“拥有”，宾语是“账户”。这给了我们一个源顶点标签Customer和一个目标顶点标签；谓语“拥有”转换为边标签，并具有从客户到其帐户的方向。我们可以对第二个短语执行类似的过程，从到Account派生出“owned_by”的边标签。AccountCustomer

An edge label’s direction comes from how we communicate about our data. When you describe your data, you use a subject, a predicate (i.e., a verb), and an object to communicate about your domain. To see this, consider how you would describe the sample data we have been using so far in this chapter. You likely are thinking something like “customers own accounts” or “bank accounts are owned by customers.” In the first phrase, the subject is “customers,” the predicate is “owns,” and the object is “accounts.” This gives us a source vertex label, Customer, and a destination vertex label, Account; the predicate “owns” translates to an edge label and has a direction from the customer to their account. We can follow a similar process for the second phrase to derive an edge label of “owned_by” from Account to Customer.

粗略地说，描述中的主语、谓语和宾语的标识创建了边标签的方向。主语是第一个顶点标签，也是边的起点。在 GSL 中，我们将其称为域。然后谓词成为你的边标签。最后，对象是边标签的目的地或范围。这意味着边标签来自域并进入范围。这给了我们两个新术语：

Loosely speaking, the identification of the subject, predicate, and object of your description creates an edge label’s direction. The subject is the first vertex label and is where an edge starts. In the GSL, we call this the domain. Then the predicate becomes your edge label. Last, the object is the destination or range of an edge label. This means the edge label comes from the domain and goes to the range. This gives us two new terms:

领域

Domain

边标签的域是边标签起源或开始的顶点标签。

The domain of an edge label is the vertex label from which the edge label originates or starts.

范围

Range

边标签的范围是边标签指向或结束的顶点标签。

The range of an edge label is the vertex label to which the edge label points or ends.

本节要说明的最后一个概念是双向边。对于我们到目前为止讨论的数据，拥有双向的边标签在语义上并不完全合理。也就是说，说客户拥有一个帐户是有意义的，但说“一个帐户拥有一个客户”是没有意义的。我们必须将边标签改为“一个帐户由一个客户拥有”。

The last concept to illustrate in this section is a bidirectional edge. For the data we have talked about so far, it doesn’t exactly make semantic sense to have an edge label that goes in both directions. That is, it is meaningful to say that a customer owns an account, but it doesn’t make sense to say “an account owns a customer.” We have to change the edge label to say “an account is owned by a customer.”

为了更好地说明双向边标签，让我们在示例中添加客户之间的关系。具体来说，让我们添加一个连接家庭成员客户的边标签。这是一个更好的说明双向边标签的示例，如图2-10所示。

To best illustrate an edge label that is bidirectional, let’s add relationships between customers into our example. Specifically, let’s add an edge label that connects customers who are family members. This is a better example to illustrate a bidirectional edge label and is shown in Figure 2-10.

图 2-10.使用带有两个箭头的线来表示边标签是双向的

在这个模型中，我们指出客户可以是其他客户的家庭成员。我们将这种关系解释为互惠关系：如果你是别人的家庭成员，他们也是你的家庭成员。我们在 GSL 中使用两端带箭头的线对此进行建模，并称此边标签为双向或双向的。

In this model, we are indicating that customers can be family members of other customers. We interpret this type of relationship as a reciprocal relationship: if you are a family member to someone else, they are also your family member. We model this in the GSL using a line with an arrow on both ends and say that this edge label is bidirectional or bidirected.

第一次遇到方向时，它可能是一个难以理解的概念。在图开发中，思考方向的最佳方法之一来自于您如何谈论数据。我们建议创建数据描述并确定如何解释其中的关系。这有助于在心理上将您对数据关系的概念理解转化为边缘标签的方向。

When first encountering direction, it can be a tricky concept to wrap your head around. In graph development, one of the best ways to think about direction comes from how you speak about your data. We recommend creating a description of your data and identifying how you would explain the relationships within it. This helps mentally translate your conceptual understanding of your data’s relationships into an edge label’s direction.

自引用边缘标签

Self-Referencing Edge Labels

在没有明确指出的情况下，我们在图 2-10中引入了一个新概念，现在我们想对其进行定义。如果一条边的起点和终点位于同一个顶点标签上，我们就称其为自引用边标签。在 GSL 中，我们会将其绘制并标注为如图 2-11所示。

Without explicitly calling it out, we introduced a new concept in Figure 2-10 that we would like to define now. If an edge starts and ends on the same vertex label, we say that is a self-referencing edge label. In the GSL, we would draw and notate this as seen in Figure 2-11.

自我参照

Self-referencing

自引用边标签是指边标签的域和范围是相同的顶点标签。

A self-referencing edge label is where the edge label’s domain and range are the same vertex label.

图 2-11. GSL 中使用边标签来指示双向、自引用边

图 2-11是说明以相同顶点标签开始和结束的边标签的正确方法。我们称这是一个自引用边标签。在图 2-11的情况下，它也是一个双向边标签。但是，并非所有自引用边标签都是双向的。

Figure 2-11 is the correct way to illustrate an edge label that starts and ends on the same vertex label. We say that this is a self-referencing edge label. In the case of Figure 2-11, it is also a bidirected edge label. However, not all self-referencing edge labels are bidirected.

图的多重性

Multiplicity of Your Graph

当您开始深入研究使用图进行数据建模时，您可能需要一种方法来指示图中不同顶点标签之间可以存在多少种关系。

When you start diving into data modeling with graphs, you will probably want a way to indicate how many relationships can exist between different vertex labels in your graph.

关于这个话题我们有一些好消息。在大多数图模型中，描述关系数量只有一个选项：多个。

We have some good news on this topic. There is only one option for describing the number of relationships in most graph models: many.

在 DataStax Graph 和大多数其他图数据库中，所有边标签都代表多对多关系。这意味着，任何顶点都可以通过特定的边标签与许多其他顶点相连。在 ERD 中，这称为many-to-many；在 UML 中使用 0..* 到 0..*。有时，这也被称为m:n关系社区中的关系。

In DataStax Graph and in most other graph databases, all edge labels represent many-to-many relationships. Meaning, any vertex can have many other vertices connected to it by a particular edge label. In an ERD, this is called many-to-many; in UML you use 0..* to 0..*. Sometimes, this is also referred to as an m:n relationship within the relational community.

我们使用术语多重性来描述这一概念：

We use the term multiplicity to describe this concept:

多重性

Multiplicity

多重性是一组可采用的允许大小范围的规范。也就是说，多重性描述了沿着特定边标签与给定顶点相邻的顶点组可采用的允许大小范围^。2

Multiplicity is a specification of the range of allowable sizes that a group may assume. Namely, multiplicity describes the range of allowable sizes that the group of vertices adjacent to a given vertex along a particular edge label may assume.²

为了正确性和清晰度，我们在讨论图模式中的边标签建模时专门使用术语“多重性”。让我们更深入地探讨一下在图模型中应用多重性定义时可用的两个选项。

For correctness and clarity, we exclusively use the term multiplicity when talking about modeling edge labels within a graph schema. Let’s dig a little deeper into the two options available when you apply the definition of multiplicity in your graph model.

在 GSL 中建模多样性

Modeling multiplicity in the GSL

将多重性应用于图模式归结为理解可能存在的不同类型的相邻顶点组。只有两种：一组或一个集合。

The application of multiplicity to your graph’s schema comes down to understanding the different kinds of groups of adjacent vertices that are possible. There are only two: a set or a collection.

放

Set

集合是一种存储唯一值的抽象数据类型。

A set is an abstract data type that stores unique values.

收藏

Collection

集合是一种存储非唯一值的抽象数据类型。

A collection is an abstract data type that stores nonunique values.

在一组相邻顶点中，一个顶点的实例只能有一个。在一组相邻顶点中，一个顶点的实例可以有很多个。我们在图 2-12中说明了这两个概念之间的区别。

In a set of adjacent vertices, there can be only one instance of a vertex. In a collection of adjacent vertices, there could be many instances of a vertex. We illustrate the difference between these concepts in Figure 2-12.

图 2-12。对特定边标签的相邻顶点组应用多重性的两种选项

图 2-12左侧的图显示，相邻的顶点组Michael是一个集合：{acct_0}。这意味着我们希望数据库中的客户和帐户之间最多有一条边。图 2-12右侧的图显示，相邻的顶点组Michael是一个集合：[acct_0, acct_0]。这意味着我们希望数据库中的客户和帐户之间有多条边。需要多条边的一个例子是，当您想要表示客户既是帐户的管理员又是帐户的用户时。

The graph on the left in Figure 2-12 shows that the group of vertices adjacent to Michael is a set: {acct_0}. This means that we want to have at most one edge between a customer and an account in our database. The graph on the right in Figure 2-12 shows that the group of vertices adjacent to Michael is a collection: [acct_0, acct_0]. This means that we want to have many edges between a customer and an account in our database. An example of when you would like many edges is when you want to represent that a customer is both administrator and user of an account.

提示

最有可能需要决定多重性类型的场合是当您在边缘上建模时间时。您是否只想要数据库中最新的边缘？那么您可以将边缘视为一个集合。您是否想要随时间变化的所有边缘？那么您可以将边缘视为一个集合。我们将在第7章、第 9 章和第12 章中介绍边缘上的时间。

The most likely occasion for needing to decide about the multiplicity type is when you are modeling time on your edges. Do you want only the most recent edge in your database? Then you are thinking of your edge as a set. Do you want all of your edges over time? Then you are thinking of your edges as a collection. We will cover time on edges in Chapters 7, 9, and 12.

让我们看看如何在 GSL 中模拟图 2-12中两个图之间的差异。

Let’s look at how we would model the differences between the two graphs from Figure 2-12 in the GSL.

图 2-13显示了我们如何使用 GSL 中的一条线来表示我们希望两个顶点之间最多有一条边。

Figure 2-13 shows how we use a single line in the GSL to illustrate that we want to have at most one edge between two vertices.

图 2-13。在 GSL 中，我们使用一条线来指示何时相邻顶点组需要组成一个集合

为了能够对两个顶点之间的多条边进行建模，我们需要一种方法使一条边与另一条边不同。在图 2-12中，我们role向边添加了属性，以便每条边都不同。图 2-14显示了我们如何在 GSL 中使用双线和属性值来说明我们希望两个顶点之间有多条边。

In order to be able to model many edges between two vertices, we need a way for one edge to be different from another edge. In Figure 2-12, we added the role property to the edge so that each edge is different. Figure 2-14 shows how we use a double line and a property value in the GSL to illustrate that we want to have many edges between two vertices.

图 2-14。在 GSL 中，我们使用双线来指示何时相邻顶点组需要成为一个集合

理解多重性的诀窍在于理解你的数据。如果你需要在两个顶点之间有多条边（因为你需要将连接的顶点组作为一个集合而不是一个集合），那么你需要在边上定义一个属性，使其具有唯一性。

The trick to understanding multiplicity lies in understanding your data. If you need to have multiple edges between two vertices—because you need the group of connected vertices to be a collection rather than a set—then you need to define a property on the edge that makes it unique.

完整示例图模型

Full Example Graph Model

使用GSL，表2-1中的数据可以转换成图2-15所示的概念图模型。

Using GSL, the data from Table 2-1 translates into the conceptual graph model shown in Figure 2-15.

图 2-15。我们第一个示例的起点：金融服务客户数据的基本模型

我们参考以概念图模型的形式呈现在图 2-15中。此模型创建图数据库模式。此概念图模型显示了一个客户和与该客户相关的三个不同数据。这四个实体转换为四个单独的顶点标签：、、和。CustomerAccountLoanCreditCard

We refer to the image in Figure 2-15 as a conceptual graph model. This model creates your graph database schema. This conceptual graph model shows a customer and three different pieces of data related to the customer. These four entities translate to four separate vertex labels: Customer, Account, Loan, and CreditCard.

这四部分数据以三种方式相关：客户拥有账户、使用信用卡和欠贷款。这在概念图模型中创建了三个边标签：分别owns为uses、和owes。所有三个边标签都是有向的；此示例中没有双向边标签。此外，我们看到边标签uses和owes在两个顶点之间最多有一条边，而owns可以有多条边。

These four pieces of data are related in three ways: customers own accounts, use credit cards, and owe loans. This creates three edge labels in the conceptual graph model: owns, uses, and owes, respectively. All three edge labels are directed; there are no bidirectional edge labels in this example. Further, we see that the edge labels uses and owes will have at most one edge between two vertices, whereas owns can have many edges.

图 2-15中要探索的最后一部分是每个顶点标签上显示的属性。这些是我们可以在表 2-1的数据中找到的属性。ACustomer有两个属性：customer_id和name。Account、CreditCard和Loan顶点标签分别只有一个属性：acct_id、cc_num和loan_id。在每种情况下， 属性都是数据的唯一标识符。

The final piece of Figure 2-15 to explore is the properties shown on each vertex label. These are the properties that we can find in the data from Table 2-1. A Customer has two properties: customer_id and name. The Account, CreditCard, and Loan vertex labels each have only one property: acct_id, cc_num, and loan_id, respectively. In each case, the property is the unique identifier for the data.

理解图 2-15和我们在图 2-4中展示的实例数据之间的区别非常重要。图 2-15显示了使用 GSL 的数据库模式的概念图模型。图 2-4显示了图数据库中的数据。

It is important to understand the difference between Figure 2-15 and the instance data we showed in Figure 2-4. Figure 2-15 shows the conceptual graph model for your database schema using GSL. Figure 2-4 shows what the data will look like in your graph database.

数据建模

Data Modeling

图数据建模与关系数据建模非常相似；主要区别在于对实体之间关系的考虑。图技术针对关系优先的数据组织进行了优化，以便提供对数据库中实体关系的直接访问。鉴于此，如果实体之间的关系是数据的最重要特征，您将需要探索图技术。

Graph data modeling is very similar to relational data modeling; the main difference is in the consideration of relationships between entities. Graph technology is optimized for relationship-first data organization so as to provide direct access to an entity’s relationships in the database. Given this, you will want to explore graph technology if the relationships between your entities are the most important features of your data.

与关系技术相比，图技术的创建是为了最大限度地减少从思维模型到数据存储和检索的转变。使用图技术，概念数据模型就是实际的物理数据模型。也就是说，您不必专门进行任何物理数据建模，因为图数据库仅基于逻辑模型优化存储和物理布局。这是通过将顶点的边存储在可直接访问与顶点关联的边的结构中来实现的。

In contrast to relational technology, graph technology was created to minimize the transition from mental model to data storage and retrieval. With graph technologies, the conceptual data model is the actual physical data model. That is, you don’t have to specifically do any physical data modeling, as the graph database optimizes the storage and physical layout based on the logical model alone. This is achieved by storing the edges for a vertex in structures that give direct access to the edges associated to a vertex.

根据我们的经验，从思维模型到数据存储的更短转换是架构师从关系技术转向图技术的主要原因之一。使用图技术时，您可以绘制一个图像，既代表数据的概念理解，也代表数据的物理组织。从概念到物理数据组织的这种更短的解​​释创造了一种更强大的方式来设想、讨论和应用数据中的关系。如果没有图思维和技术，这以前是无法实现的。

In our experience, the shorter translation from mental model to data storage is one of the primary reasons architects are turning from relational to graph technologies. When using graph technology, you can draw one image that represents both the conceptual understanding and the physical organization of your data. This shorter interpretation from conceptual to physical data organization creates a more powerful way to envision, discuss, and apply the relationships within your data. Without graph thinking and technology, this was previously unachievable.

理解图数据

Understanding Graph Data

应用图论增强了在应用程序中使用图技术的吸引力。图技术让您能够了解数据是否连接以及连接程度。具体而言，诸如邻域和度等概念可以开启对数据的全新理解，而这是关系技术无法实现的。

Applied graph theory empowers the appeal of using graph technology in your application. Graph technology gives you the means to understand both if and how well your data is connected. Specifically, concepts such as neighborhoods and degree open up to a new understanding about your data that is not possible with relational technologies.

图模式和图数据之间的细微差别非常重要。向您的团队介绍图技术需要学习新术语、概念和应用程序。避免自己受阻的最有效方法之一是了解哪些概念适用于数据库建模，哪些概念适用于应用程序级别的数据分析。

The nuances between the worlds of graph schema and graph data are very important. Introducing graph technology to your team comes with a learning curve of new terms, concepts, and applications. One of the most effective ways to keep yourselves from being blocked is to understand which concepts apply to database modeling and which apply to data analysis at the application level.

混合数据库设计与应用程序目的

Mixing Database Design with Application Purpose

我们从经验中了解到，团队经常混淆图数据分析和图模式的概念。我们认为，交换图模式和图数据的术语等同于混淆以下两个概念：饼图和外键约束。

We have learned from our experiences that teams often confuse concepts about graph data analysis and graph schema. As we see it, interchanging terms from graph schema and graph data is the same as confusing the following two concepts: pie charts and foreign key constraints.

让我们来解析一下。

Let’s unpack that.

关系技术非常适合用于设置数据库，以创建报告和数据摘要，例如饼图。饼图之类的东西可以直观地显示有关数据的指标。应用程序（饼图）与关系架构设计完全不同且不相关，例如在表之间选择外键约束。

Relational technologies are great for setting up a database that creates reports and summaries of data, like pie charts. Something like a pie chart visualizes a metric about the data. The application (the pie chart) is a completely different, and unrelated, concept from relational schema design, like selecting foreign key constraints between tables.

关系数据库的一个应用是创建饼图，并且数据库的模式需要使用外键进行设计才能实现。

An application of your relational database is to create pie charts, and the database’s schema requires designing with foreign keys to make it possible.

在开始使用图技术时，这种区别同样适用。设置图数据库后，您可以使用它来了解数据的连通性。具体来说，您可以找到图中两个顶点之间的距离。这是在应用程序级别，并使用数据来了解数据内的连接。这可以通过创建具有顶点标签、边标签和属性的图数据库模式来实现。

When getting started with graph technology, this same distinction applies. After you have set up your graph database, you use it to understand the connectivity of your data. Specifically, you can find the distance between two vertices in your graph. This is at the application level and uses data to understand the connections within the data. This is made possible by creating a graph database schema with vertex labels, edge labels, and properties.

图数据库的一个应用是计算顶点之间的距离，数据库的模式需要使用边标签和顶点标签进行设计才能实现这一点。

An application of your graph database is to calculate the distance between vertices, and the database’s schema requires designing with edge labels and vertex labels to make that possible.

这里重要的一点是了解创建数据库模式和分析图数据之间的区别。

The important takeaway here is to understand the differences between creating database schema and analyzing graph data.

到目前为止，图思维的洪流已经带来了许多术语和复杂性。在本章中，我们希望清楚地描述创建数据库模型的技术以及一些用于分析图数据的技术。

Up until now, the flood of graph thinking has introduced many waves of terminology and complexity. In this chapter, we hope to have clearly delineated the techniques for creating database models as well as a few for analyzing graph data.

企业为何关心 C360？

Why Do Businesses Care About C360?

消费者以全渠道的方式与您的公司互动；他们可以从您的移动或网络应用程序无缝过渡到您的社交媒体源和实体店面。 在所有这些渠道中，客户都会对您的品牌产生综合的认知。通过创建统一的数字体验来满足这一期望的公司的收入增长高达 10%。据测算，这些收入增长速度是未统一客户数字体验的公司收入增长速度的两到三倍^。1

Consumers interact with your company in an omnichannel fashion; they seamlessly transition from your mobile or web applications to your social media feeds and physical storefronts. Across all of these channels, they are experiencing an integrated perception of your brand. Companies that match this expectation by creating a unified digital experience are seeing revenue increases of up to 10%. These revenue increases are measured to be two to three times faster than those of companies that have not unified their customers’ digital experiences.¹

收入增长背后的秘密在于一款整合所有客户数据的应用程序。将所有客户的数据整合到一个应用程序中，可以反映出每位客户对您品牌的体验。换句话说，这是一个 C360 应用程序。

The secret ingredient behind this observed revenue growth is an application that unifies all customer data. Bringing together all of your customers’ data into an application mirrors each customer’s experience with your brand. In other words, it is a C360 application.

早期创新者部署了有趣的 C360 应用程序，其创意案例数不胜数。其中一个比较独特的案例来自百度（中国的谷歌）和肯德基。通过统一的数据平台，百度与肯德基联手提供订单推荐。他们的协作解决方案可以识别客户、访问他们的订单历史记录并返回订单推荐。事实证明，这两个行业之间的数据集成是 C360 技术的一个独特且有利可图的例子。

There are myriad creative examples of early innovators who have deployed interesting C360 applications. One of the more unique examples comes from Baidu (the Google of China) and Kentucky Fried Chicken. Through a unified data platform, Baidu teamed up with KFC to deliver order recommendations. Their collaborative solution identifies customers, accesses their order history, and returns order recommendations. This integration of data across these two industries has proven to be a unique and profitable example of C360 technologies.

C360 应用程序是将图思维应用于您的业务的起点。正确实施此应用程序为将图数据引入系统架构奠定了坚实的基础。我们发现，架构师和系统设计师最常犯的错误之一是使用图技术从概念模型过快转向实现细节。这里还有更多需要考虑的地方，我们希望利用我们的经验指导您完成本章其余部分的评估。

A C360 application is the starting place for implementing graph thinking in your business. Getting this right provides a solid foundation for introducing graph data into your system’s architecture. We have found that one of the most common mistakes made by architects and system designers is to move too quickly from the conceptual model to implementation details with graph technologies. There is more to consider here, and we want to use our experience to guide you through your own evaluation throughout the rest of this chapter.

数据模型

Data Models

在就概念模型达成一致之后（如图2-1所示），您就可以继续设计关系数据库了。传统上，您会创建实体关系图(ERD)。ERD 是数据模型的逻辑表示，是关系数据库设计的典型起点。

After agreeing on a conceptual model, like that shown in Figure 2-1, you can move on to the design of your relational database. Traditionally, you would create an entity-relationship diagram, or ERD. An ERD is a logical representation of your data model and is a typical starting point for a relational database design.

在图 3-2中，每个方块代表一个将成为关系数据库中表的实体。每个方块内列出了每个实体的属性或描述性属性。如数据所示，每个实体都有一个唯一标识符。客户由其唯一标识，帐户由其唯一标识，等等。客户还有姓名，在大型应用程序中，还有其他属性。customer_idacct_id

In Figure 3-2, each square represents an entity that will become a table in the relational database. The attributes, or descriptive properties about each entity, are listed within each square. As already seen in the data, each entity will have a unique identifier. A customer will be uniquely identified by its customer_id, an account by its acct_id, and so on. Customers also have names and, in larger applications, other attributes.

图 3-2。此 C360 应用程序的关系实现的实体关系图

图 3-2中实体之间的菱形表示从一个实体到另一个实体的连接。连接的基数在菱形的上方和下方或左侧和右侧表示。在此数据中，我们有两种类型的连接：一对多和多对多。

The diamond shapes between entities in Figure 3-2 represent the connection from one entity to another. The cardinality of the connection is indicated above and below or to the left and right of the diamond shape. In this data, we have two types of connections: one-to-many and many-to-many.

让我们从客户和信用卡之间的一对多连接开始。在此示例中，一个客户可以有多张信用卡，但一张信用卡只能有一个客户。这种一对多连接描述了客户和信用卡之间的基数，如图 3-2所示。n to 1

Let’s start with the one-to-many connection that is shown between customers and credit cards. In this example, a customer can have many credit cards, but a credit card can only have one customer. This one-to-many connection describes the cardinality between customers and credit cards and is illustrated with the n to 1 connection between customers and credit cards in Figure 3-2.

我们在数据中看到的另一种连接类型是多对多连接。我们的数据中有两种多对多连接：客户与账户和客户与贷款。从数据中我们知道，一个客户可以拥有多个账户，一个账户可以拥有多个客户。贷款也是如此。我们称客户与贷款为多对多连接，并在图 3-2n to m中用连接的符号来说明这一点。

The other type of connection we see in our data is a many-to-many connection. There are two many-to-many connections in our data: customers to accounts and customers to loans. We know from our data that a customer can have many accounts, and one account can have many customers. The same is true for loans. We say that customers to loans is a many-to-many connection and illustrate this in Figure 3-2 with the n to m notation on the connection.

在创建表和插入数据之前，我们需要将逻辑数据模型转换为物理数据模型。具体来说，我们需要将图 3-2所示的 ERD 中的实体和连接转换为具有主键和外键的表。

Before creating tables and inserting data, we need to translate our logical data model into a physical data model. Specifically, we need to translate the entities and connections from the ERD illustrated in Figure 3-2 into tables with primary and foreign keys.

对于此实现，我们需要两种类型的键：主键和外键。主键是唯一标识的数据，例如客户的 ID 或信用卡号，我们将使用它来访问表中的信息。外键是一段唯一标识数据，我们将使用它来访问不同表中的信息，例如将客户的 ID 与信用卡信息一起存储。我们将客户的 ID 与客户的信用卡信息一起存储，以便我们可以使用它在另一个表（即客户表）中查找他们的所有信息。

We need two types of keys for this implementation: primary and foreign keys. A primary key is a uniquely identifying piece of data, such as a customer’s ID or credit card number, that we will use to access the information in its table. A foreign key is a uniquely identifying piece of data that we will use to access the information in a different table, such as storing a customer’s ID alongside their credit card information. We store a customer’s ID with the customer’s credit card information so that we can use it to look up all of their information in a different table, namely in the customer table.

让我们看一下图 3-2中的键和数据如何映射到图 3-3所示的物理数据模型中。

Let’s take a look at how the keys and data from Figure 3-2 map into the physical data model shown in Figure 3-3.

图 3-3。此 C360应用程序关系实现的物理数据模型

我们期望在图 3-3中看到至少四个表— — 每个实体一个表。具体来说，图 3-3 中每个实体类型都有一个表：客户、账户、贷款和信用卡。对于每个表，我们都可以看到描述实体的附加属性。每个实体最重要的属性是其主键。每个主键在行旁边用 表示PK。每个表的主键分别是customer_id、acct_id、loan_id和cc_num。这些是我们将用来查找表中特定信息行的唯一标识符。

We expected to see at least four tables in Figure 3-3—one table for each entity. Specifically, Figure 3-3 has one table per entity type: customer, account, loan, and credit card. For each of those tables, we see additional attributes that describe the entity. The most important attribute for each of these entities is its primary key. Each primary key is indicated with a PK next to the row. The primary keys we have for each table are the customer_id, acct_id, loan_id, and cc_num, respectively. These are the unique identifiers that we will use to look up a specific row of information in the table.

在讨论图 3-3中的其他两个表之前，让我们先检查一下该CreditCard表。此表既有主键又有外键。我们在此表中使用外键来跟踪我们在 ERD 中创建的一对多关系。是外键customer_id（用 表示FK），它使我们能够将信用卡信息关联回唯一客户。在物理数据模型中构建一对多关系可以像添加外键以将您重新连接到另一个实体表一样简单。

Before we talk about the other two tables in Figure 3-3, let’s examine the CreditCard table. This table has both a primary key and a foreign key. We are using a foreign key in this table to track the one-to-many relationship we created in our ERD. The customer_id is the foreign key (indicated with an FK) that will give us the ability to relate the credit card information back to a unique customer. Building a one-to-many relationship into your physical data model can be as easy as adding a foreign key to join you back to another entity table.

在我们的物理数据模型中要理解的最后两个表是Owns和Owes表。这些表是连接表，使我们能够在数据中物理存储多对多连接。该Owns表存储观察到的客户与其拥有的账户之间的许多连接。该Owes表存储观察到的客户与其所欠贷款之间的许多连接。由于每个客户只能拥有一个账户，并且只能欠一次贷款，因此这些连接表的主键是两个外键的组合。

The last two tables to understand in our physical data model are the Owns and Owes tables. These tables are join tables that allow us to physically store the many-to-many connections in our data. The Owns table stores the many connections observed between customers and the accounts that they own. The Owes table stores the many connections observed between customers and the loans that they owe. Since each customer can own an account only once and can owe a loan only once, the primary key of these join tables is a compound of the two foreign keys.

例如，该Owns表存储了表中每行的至少两条信息：客户的唯一标识符和帐户的唯一标识符。给定此表中的一行，我们可以访问客户的唯一标识符以重新连接到客户表，也可以访问帐户的唯一标识符以重新连接到帐户表。此连接表是在关系系统中表示多对多连接的常用方法。

For example, the Owns table stores at least two pieces of information about each row in the table: the customer’s unique identifier and the account’s unique identifier. Given one row from this table, we can access both the customer’s unique identifier to join back to the customer table and the account’s unique identifier to join back to the account table. This join table is a common way to represent a many-to-many connection in a relational system.

关系实现

Relational Implementation

给定我们的物理数据模型，让我们逐步创建表并将表 3-1中的示例数据插入到表中。

Given our physical data model, let’s walk through creating the tables and inserting our sample data from Table 3-1 into the tables.

首先，我们要创建客户表。最终的数据模型如图3-4所示。

First, we want to create the customer table. The final data model for this is shown in Figure 3-4.

图 3-4关系实现的客户表

创建客户表的SQL语句是：

The SQL statement for creating the customer table is:

CREATE TABLE Customers ( customer_id TEXT,
                         name TEXT,
                         PRIMARY KEY (customer_id));

CREATE TABLE Customers ( customer_id TEXT,
                         name TEXT,
                         PRIMARY KEY (customer_id));

我们的数据中有 5 位客户。让我们将这 5 位客户插入到此客户表中：

Our data has five customers. Let’s insert those five customers into this customer table:

INSERT INTO Customers (customer_id, name) VALUES
  ('customer_0', 'Michael'),
  ('customer_1', 'Maria'),
  ('customer_2', 'Rashika'),
  ('customer_3', 'Jamie'),
  ('customer_4', 'Aaliyah');

INSERT INTO Customers (customer_id, name) VALUES
  ('customer_0', 'Michael'),
  ('customer_1', 'Maria'),
  ('customer_2', 'Rashika'),
  ('customer_3', 'Jamie'),
  ('customer_4', 'Aaliyah');

我们关系数据库中的数据现在有一个包含五个条目的表，如图3-5所示。

Our data in our relational database now has one table with five entries, as shown in Figure 3-5.

图 3-5。关系数据库中的客户数据

接下来我们添加另外三个实体表，分别为Accounts、Loans和。它们的最终数据模型如图3-6CreditCards所示。

Next, let’s add the other three entity tables for Accounts, Loans, and CreditCards. Their final data models are shown in Figure 3-6.

图 3-6关系实现的帐户、贷款和信用卡表

我们将首先创建Accounts和的两个表Loans：

We will start by creating the two tables for Accounts and Loans:

CREATE TABLE Accounts ( acct_id TEXT,
                        created_date DATE DEFAULT CURRENT_DATE,
                        PRIMARY KEY (acct_id));

CREATE TABLE Loans ( loan_id TEXT,
                     created_date DATE DEFAULT CURRENT_DATE,
                     PRIMARY KEY (loan_id));

CREATE TABLE Accounts ( acct_id TEXT,
                        created_date DATE DEFAULT CURRENT_DATE,
                        PRIMARY KEY (acct_id));

CREATE TABLE Loans ( loan_id TEXT,
                     created_date DATE DEFAULT CURRENT_DATE,
                     PRIMARY KEY (loan_id));

接下来，我们插入Accounts和的数据Loans：

Next, let’s insert the data for Accounts and Loans:

INSERT INTO Accounts (acct_id) VALUES
  ('acct_0'),
  ('acct_5'),
  ('acct_14');

INSERT INTO Loans (loan_id) VALUES
  ('loan_18'),
  ('loan_32'),
  ('loan_80');

INSERT INTO Accounts (acct_id) VALUES
  ('acct_0'),
  ('acct_5'),
  ('acct_14');

INSERT INTO Loans (loan_id) VALUES
  ('loan_18'),
  ('loan_32'),
  ('loan_80');

此时，我们需要在关系数据库中创建最后一个实体表： 表CreditCards。由于信用卡与客户之间存在一对多关系，因此我们还需要插入客户的 ID 作为外键。我们使用以下命令创建此表：

At this point, we have one last entity table to create in our relational database: the table for CreditCards. Because credit cards have a one-to-many relationship with customers, we also need to insert the customer’s ID as a foreign key. We create this table with:

CREATE TABLE CreditCards
    ( cc_num TEXT,
      customer_id TEXT NOT NULL,
      created_date DATE DEFAULT CURRENT_DATE,
      PRIMARY KEY (cc_num),
      FOREIGN KEY (customer_id) REFERENCES Customers(customer_id));

CREATE TABLE CreditCards
    ( cc_num TEXT,
      customer_id TEXT NOT NULL,
      created_date DATE DEFAULT CURRENT_DATE,
      PRIMARY KEY (cc_num),
      FOREIGN KEY (customer_id) REFERENCES Customers(customer_id));

回顾表 3-1中的数据，我们发现每张唯一的信用卡以及拥有该卡的客户。根据这些信息，我们可以创建以下语句将这些数据插入到关系数据库中：

Looking back at the data from Table 3-1, we find each unique credit card and the customer who owns that card. From this information, we can create the following statements to insert this data into our relational database:

INSERT INTO CreditCards (cc_num, customer_id) VALUES
  ('cc_17', 'customer_0'),
  ('cc_32', 'customer_2');

INSERT INTO CreditCards (cc_num, customer_id) VALUES
  ('cc_17', 'customer_0'),
  ('cc_32', 'customer_2');

我们的关系数据库现在共有四个包含数据的表；见图3-7。

Our relational database now has a total of four tables with data; see Figure 3-7.

图 3-7。关系数据库中四个实体表的数据

Customers在我们的关系实现中要创建的最后两个表是用于从到Accounts和的多对多连接的表Loans。首先，让我们创建将客户与帐户连接的表，如图3-8所示。

The last two tables to create in our relational implementation are those for the many-to-many connections from Customers to Accounts and Loans. First, let’s create the table that will join Customers to Accounts, as illustrated in Figure 3-8.

图 3-8.从 Customers 到 Accounts 的连接表

在 SQL 中，我们使用以下命令创建该表：

In SQL, we create this table with:

CREATE TABLE Owns ( customer_id TEXT NOT NULL,
                    acct_id TEXT NOT NULL,
                    created_date DATE DEFAULT CURRENT_DATE,
                    PRIMARY KEY (customer_id, acct_id),
                    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
                    FOREIGN KEY (acct_id) REFERENCES Accounts(acct_id));

CREATE TABLE Owns ( customer_id TEXT NOT NULL,
                    acct_id TEXT NOT NULL,
                    created_date DATE DEFAULT CURRENT_DATE,
                    PRIMARY KEY (customer_id, acct_id),
                    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
                    FOREIGN KEY (acct_id) REFERENCES Accounts(acct_id));

回想一下表 3-1中的数据，我们找到以下要插入到Owns表中的数据：

Remembering the data from Table 3-1, we find the following data to insert into the Owns table:

INSERT INTO Owns (customer_id, acct_id) VALUES
  ('customer_0', 'acct_14'),
  ('customer_1', 'acct_14'),
  ('customer_2', 'acct_5'),
  ('customer_3', 'acct_0'),
  ('customer_4', 'acct_0');

INSERT INTO Owns (customer_id, acct_id) VALUES
  ('customer_0', 'acct_14'),
  ('customer_1', 'acct_14'),
  ('customer_2', 'acct_5'),
  ('customer_3', 'acct_0'),
  ('customer_4', 'acct_0');

现在我们的Owns表中有了一些数据（见图3-9），我们可以看到如何在数据中关联客户数据和帐户数据。

Now that we have some data in our Owns table (see Figure 3-9), we can see how to associate the data from a customer and an account in our data.

图 3-9.客户、账户和连接表数据

创建关系数据库的最后一步是创建Owes关联表客户与贷款之间的关联，反之亦然。此连接表的最终数据模型如图 3-10所示。

The final step in creating our relational database is to create the Owes table to associate a customer to their loan, and vice versa. The final data model for this join table is shown in Figure 3-10.

图 3-10.从 Customers 到 Loans 的连接表

在 SQL 中，我们通过以下方式在关系数据库中创建此表：

In SQL, we create this table in our relational database via:

CREATE TABLE Owes ( customer_id TEXT NOT NULL,
                    loan_id TEXT NOT NULL,
                    created_date DATE DEFAULT CURRENT_DATE,
                    PRIMARY KEY (customer_id, loan_id),
                    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
                    FOREIGN KEY (loan_id) REFERENCES Loans(loan_id));

CREATE TABLE Owes ( customer_id TEXT NOT NULL,
                    loan_id TEXT NOT NULL,
                    created_date DATE DEFAULT CURRENT_DATE,
                    PRIMARY KEY (customer_id, loan_id),
                    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
                    FOREIGN KEY (loan_id) REFERENCES Loans(loan_id));

最后，我们可以从表 3-1中的数据中提取最后一个连接，并将所有欠贷款的客户的观察结果插入到Owes表当中：

Finally, we can extract one last connection from the data in Table 3-1 and insert all observations of customers who owe loans into the Owes table:

INSERT INTO Owes (customer_id, loan_id) VALUES
  ('customer_0', 'loan_32'),
  ('customer_3', 'loan_18'),
  ('customer_4', 'loan_18'),
  ('customer_4', 'loan_80');

INSERT INTO Owes (customer_id, loan_id) VALUES
  ('customer_0', 'loan_32'),
  ('customer_3', 'loan_18'),
  ('customer_4', 'loan_18'),
  ('customer_4', 'loan_80');

我们的数据全貌在我们的关系数据库中如图 3-11所示。

The full picture of our data in our relational database is shown in Figure 3-11.

图 3-11。数据到关系数据库的完整映射